package com.coal.exam.bean;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.joda.time.DateTime;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.text.DecimalFormat;
import java.time.LocalTime;
import java.util.*;

/**
 * Created by zhangBo on 17-3-27.
 */
public class ExcelUtil<T> {
    private static final Logger logger = LoggerFactory.getLogger(ExcelUtil.class);
    private String SETTER_PFIX = "set";
    private String GETTER_PREFIX = "get";
    private InputStream is;
    private Class<?> clazz;
    private Map<String,String> dataMapping;

    /**
     * @param is
     *@param clazz 每行数据对应的实体类
     *@param dataMapping 导入excel列的标题与类属性的对应关系 k:标题 v:类属性名
     */
    public ExcelUtil(InputStream is, Class<?> clazz, Map<String,String> dataMapping){
        this.is = is;
        this.clazz = clazz;
        this.dataMapping = dataMapping;
    }

    public Workbook createWorkBook(InputStream is){
        Workbook wb= null;
        try {
            wb = new XSSFWorkbook(is);
        }catch (IOException e1) {
            e1.printStackTrace();
        }
        return wb;
    }


    public void export(List<T> list,Workbook workbook) throws Exception{
        Sheet sheet = workbook.getSheetAt(0);
        Map<Integer,String> titleMapping = getTitleIndex(sheet.getRow(0), dataMapping.keySet());
        for(int i = 1;i<=list.size();i++){
            T o = list.get(i - 1);
            Row row = sheet.getRow(i);
            if(row == null){
                row = sheet.createRow(i);
            }
            for(Integer key:titleMapping.keySet()){
                Cell cell = row.getCell(key);
                if(cell == null){
                    cell = row.createCell(key);
                }
                String prop = titleMapping.get(key);
                String method = dataMapping.get(prop);
                cell.setCellValue(getValue(method,o));
            }
        }


    }

    public String getValue(String method,T t) throws Exception{
        Method declaredMethod = t.getClass().getMethod(GETTER_PREFIX + method.substring(0, 1).toUpperCase() + method.substring(1, method.length()));
        Object obj = declaredMethod.invoke(t);
        if(obj instanceof Date){
            return new DateTime((Date) obj).toString("yyyy-MM-dd");
        }
        return obj == null?"":obj.toString();
    }


    /**
     *
     *@author ray

     *@return
     *
     *2015年9月7日 下午4:29:28
     */
    @SuppressWarnings("unchecked")
    public List<T> importExcel() throws Exception{
        List<T> list = new ArrayList<T>();
        Workbook wb = createWorkBook(is);
        Object obj = null;
        Sheet sheet = wb.getSheetAt(wb.getActiveSheetIndex());
        int lastRow = sheet.getLastRowNum();
        Row row = null;
        Cell cell = null;
        Map<Integer,String> titleMapping = getTitleIndex(sheet.getRow(0), dataMapping.keySet());

        for(int i = 1;i<=lastRow;i++){
            row = sheet.getRow(i);
            try {
                obj = clazz.newInstance();
            } catch (InstantiationException e) {
                e.printStackTrace();
            } catch (IllegalAccessException e) {
                e.printStackTrace();
            }
            for(int j = 0;j<=row.getLastCellNum();j++){
                if(titleMapping.containsKey(j)){
                    cell = row.getCell(j);
                    String value = getCellValue(cell);
                    //当前列对应的标题
                    String title = titleMapping.get(j);
                    //标题对应的类属性
                    String fieldName = dataMapping.get(title);
                    logger.debug("设置属性值：属性：[{}],值：[{}]",fieldName,value);
                    setValue(obj, fieldName, value);
                }
            }
            list.add((T) obj);
        }
        try {
            wb.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
        return list;
    }

    /**
     * excel 列数对应的标题
     *@author ray
     *@param row
     *@return
     *
     *2015年9月7日 下午4:47:59
     */
    private Map<Integer,String> getTitleIndex(Row row, Set<String> titles){
        Map<Integer,String> map = new HashMap<Integer,String>();
        Cell cell = null;
        for(int j = 0;j<row.getLastCellNum();j++){
            cell = row.getCell(j);
            String value = null;
            try {
                value = getCellValue(cell);
            } catch (Exception e) {
                e.printStackTrace();
            }
            for(String str:titles){
                if(value.equals(str)){
                    map.put(j,str);
                    break;
                }
            }
        }
        return map;
    }
    /**
     * 获取Cell中的值
     *@author ray
     *@param cell
     *@return
     *
     *2015年9月7日 下午4:43:11
     */
    private String getCellValue(Cell cell) throws Exception{
        String value = "";
        if(cell == null){
//            throw new Exception("单元格为空！");
            return value;
        }
        logger.debug("cell type:[{}]",cell.getCellType());
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_BOOLEAN:
                boolean bvalue =  cell.getBooleanCellValue();
                value = String.valueOf(bvalue);
                break;
            case Cell.CELL_TYPE_NUMERIC:
                DecimalFormat df = new DecimalFormat("0");
                Double dvalue = cell.getNumericCellValue();
                value = df.format(dvalue);
//                value = String.valueOf(dvalue);
                break;
            case Cell.CELL_TYPE_BLANK:
                break;
            case Cell.CELL_TYPE_FORMULA:
                value = cell.getCellFormula();
                break;
            default:
                value = cell.getStringCellValue();
                break;
        }

        return value;
    }

    /**
     * 通过反射设置属性值
     *@author ray
     *@param obj
     *@param fieldName
     *@param value
     *
     *2015年9月7日 下午5:14:47
     */
    private void setValue(Object obj,String fieldName,Object value) throws InvocationTargetException, IllegalAccessException {
        Method[] methods = obj.getClass().getMethods();
        String name = "";
        for(Method method:methods){
            name = method.getName();
            if(name.startsWith(SETTER_PFIX) && name.substring(SETTER_PFIX.length()).equalsIgnoreCase((fieldName))){
                method.invoke(obj, value);
            }
        }
    }

}
